﻿using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
using System.Reflection;
using System.ComponentModel.DataAnnotations.Schema;
using System.IO;

namespace SubstrateHelper.Helpers
{
    public class OracleDbHelper
    {
        public static List<T> GetData<T>(string sql) where T:new()
        {
            try
            {
                string connstr = Global.Configuration.GetConnectionString("OracleConnection");

                using (var conn = new OracleConnection(connstr))
                {
                    conn.Open();
                    using (OracleCommand cmd = new OracleCommand())
                    {

                        OracleDataReader reader = null;
                        try
                        {
                            cmd.Connection = conn;
                            cmd.CommandText = sql;                           
                            reader = cmd.ExecuteReader();

                            var typeItem = typeof(T);
                            var itemProperties = typeItem.GetProperties();
                            List<KeyValuePair<PropertyInfo, string>> props = new List<KeyValuePair<PropertyInfo, string>>();
                            foreach(var prop in itemProperties)
                            {
                                var attr = Attribute.GetCustomAttribute(prop, typeof(ColumnAttribute)) as ColumnAttribute;
                                KeyValuePair<PropertyInfo, string> pp;
                                
                                if (attr != null)
                                {
                                    pp = new KeyValuePair<PropertyInfo, string>(prop, attr.Name);
                                }
                                else
                                {
                                    pp = new KeyValuePair<PropertyInfo, string>(prop, null);
                                }
                                props.Add(pp);
                            }

                            List<T> results = new List<T>();
                            while (reader.Read())
                            {
                                T item = new T();
                                int size = reader.FieldCount;

                                
                                

                                for (int i = 0; i < size; i++)
                                {
                                    //var typeName = reader.GetDataTypeName(i);
                                    var name = reader.GetName(i);
                                    var val = reader.GetValue(i);
                                    if(typeof(DBNull) == val.GetType())
                                    {
                                        val = null;
                                    }

                                    var pp = props.Where(x => x.Value == name || x.Key.Name.ToUpper().CompareTo(name.ToUpper())==0).FirstOrDefault();
                                    
                                    pp.Key.SetValue(item, val);

                                }

                                results.Add(item);

                            }
                            reader.Close();
                            return results;
                        }
                        catch (Exception ex)
                        {
                            return null;
                        }
                        
                    }
                    
                    conn.Close();
                }

                return new List<T>();
            }
            catch(Exception ex)
            {
                return new List<T>();
            }
            
        }

        public static List<List<string>> GetData(string sql)
        {
            try
            {
                string connstr = Global.Configuration.GetConnectionString("OracleConnection");

                using (var conn = new OracleConnection(connstr))
                {
                    conn.Open();
                    using (OracleCommand cmd = new OracleCommand())
                    {

                        OracleDataReader reader = null;
                        try
                        {
                            cmd.Connection = conn;
                            cmd.CommandText = sql;
                            reader = cmd.ExecuteReader();


                            List<List<string>> results = new List<List<string>>();
                            

                            
                            while (reader.Read())
                            {
                               
                                int size = reader.FieldCount;
                                if (results.Count <= 0)//加入列名称
                                {
                                    List<string> header = new List<string>();
                                    for (int i = 0; i < size; i++)
                                    {
                                        var typeName = reader.GetDataTypeName(i);
                                        var name = reader.GetName(i);
                                        header.Add(name);
                                    }
                                    results.Add(header);
                                }

                                List<string> item = new List<string>();
                                for (int i = 0; i < size; i++)
                                {
                                    //var typeName = reader.GetDataTypeName(i);
                                    
                                    var val = reader.GetValue(i).ToString();                                  

                                    item.Add(val);

                                }

                                results.Add(item);

                            }
                            reader.Close();
                            return results;
                        }
                        catch (Exception ex)
                        {
                            return null;
                        }

                    }

                    
                }

                return null;
            }
            catch (Exception ex)
            {
                return null;
            }
            
        }


        public static string SaveDataToExcel(string sql,string path)
        {
            try
            {
                var data = GetData(sql);
                using (FileStream stream = new FileStream(path, FileMode.OpenOrCreate))
                {
                    StreamWriter writer = new StreamWriter(stream);

                    foreach(var line in data)
                    {
                        string strLine="";
                        foreach(var col in line)
                        {
                            strLine += $"{col},";
                        }
                        writer.WriteLine(strLine);
                    }

                    writer.Close();
                    stream.Close();
                }
                

            }
            catch(Exception ex)
            {
                return ex.Message;
            }
            return null;
        }


        //public static string GetMaterials(ProcessBase.Models.Order order,out List<string> lotNos, out List<double> quantities)
        //{
            
        //    lotNos = new List<string>();
        //    quantities = new List<double>();
        //    try
        //    {
        //        string connstr = Global.Configuration.GetConnectionString("OracleConnection");

        //        using (var conn = new OracleConnection(connstr))
        //        {
        //            conn.Open();
        //            using (OracleCommand cmd = new OracleCommand())
        //            {
        //                OracleDataReader reader = null;
        //                try
        //                {
        //                    cmd.Connection = conn;
        //                    cmd.CommandText = $"select 备料计划表头主键 from V_MES_HX_PICK where 工单主键 = '{order.cpmohid}'";
        //                    var moID = cmd.ExecuteScalar().ToString();//工单主键                           

        //                    if (!string.IsNullOrWhiteSpace(moID))
        //                    {
                                
        //                    }


        //                    List<List<string>> results = new List<List<string>>();



        //                    while (reader.Read())
        //                    {

        //                        int size = reader.FieldCount;
        //                        if (results.Count <= 0)//加入列名称
        //                        {
        //                            List<string> header = new List<string>();
        //                            for (int i = 0; i < size; i++)
        //                            {
        //                                var typeName = reader.GetDataTypeName(i);
        //                                var name = reader.GetName(i);
        //                                header.Add(name);
        //                            }
        //                            results.Add(header);
        //                        }

        //                        List<string> item = new List<string>();
        //                        for (int i = 0; i < size; i++)
        //                        {
        //                            //var typeName = reader.GetDataTypeName(i);

        //                            var val = reader.GetValue(i).ToString();

        //                            item.Add(val);

        //                        }

        //                        results.Add(item);

        //                    }
        //                    reader.Close();
                            
        //                }
        //                catch (Exception ex)
        //                {
        //                    return null;
        //                }

        //            }


        //        }

        //        return null;
        //    }
        //    catch(Exception ex)
        //    {
        //        return ex.Message;
        //    }


            
        //}
        
    }
}
